﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;

/// <summary>
/// Summary description for DataBase
/// </summary>
public static class DataBase
{
    static string connStr = String.Format("server={0};user id={1}; password={2};" +
    "database=FootballTracker; pooling=false", "FootballTracker.db.8620285.hostedresource.com",
    "FootballTracker", "Scarecr0w!");

    static MySql.Data.MySqlClient.MySqlConnection mycon = new MySqlConnection(connStr);



    /// <summary>
    /// Grabs an Item in the specified Table with the specified ID column that matches the specified ID.
    /// </summary>
    /// <param name="Table">The Table to grab info by.</param>
    /// <param name="IDCol">The Column to use for filtering</param>
    /// <param name="ID">The value to filter by.</param>
    /// <returns>Dataset filled with the info specified by the parameters.</returns>
    public static DataSet GrabAllWithID(string Table, string IDCol, string ID)
    {
        MySqlDataAdapter adapter = new MySqlDataAdapter();
        DataSet ds = new DataSet();
        string sqlStatement = string.Format("Select * From {0} Where {1} = @ID", Table, IDCol);

        MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
        cmd.Parameters.AddWithValue("@ID", ID);
        mycon.Open();
        adapter.SelectCommand = cmd;
        adapter.Fill(ds);
        mycon.Close();
        return ds;
    }

    private static DataSet GetAllFromTable(string table)
    {
        MySqlDataAdapter adapter = new MySqlDataAdapter();
        DataSet ds = new DataSet();
        string sqlStatement = string.Format("Select * From {0}", table);

        MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);

        adapter.SelectCommand = cmd;
        adapter.Fill(ds);

        return ds;
    }

    public static class Teams
    {
        enum Columns { ID, TeamName, Website, IconLocation }

        #region Public Methods
        public static void InsertTeam(Team team)
        {
            String sqlStatement = "INSERT INTO Teams(TeamName, Website, IconLocation) VALUES (@team, @website, @iconLocation)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@team", team.TeamName);
            cmd.Parameters.AddWithValue("@website", team.Website);
            cmd.Parameters.AddWithValue("@iconLocation", team.IconURL);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            team.ID = (int)cmd.LastInsertedId;

        }

        public static void UpdateTeam(Team team)
        {
            String sqlStatement = "UPDATE Teams SET TeamName = @team, Website = @website, IconLocation = @iconLocation WHERE ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@team", team.TeamName);
            cmd.Parameters.AddWithValue("@website", team.Website);
            cmd.Parameters.AddWithValue("@iconLocation", team.IconURL);
            cmd.Parameters.AddWithValue("@ID", team.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();
        }

        public static void DeleteTeam(Team team)
        {
            String sqlStatement = "Delete From Teams Where ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@ID", team.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            team = null;
        }



        public static Team GetTeam(string teamName)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Teams", "TeamName", teamName);
            Team team = null;
            if (ds.Tables[0].Rows.Count > 0)
                team = MakeTeamFromRow(ds.Tables[0].Rows[0]);
            return team;
        }

        public static Team GetTeamWithID(string ID)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Teams", "ID", ID);
            Team team = null;
            if (ds.Tables[0].Rows.Count > 0)
                team = MakeTeamFromRow(ds.Tables[0].Rows[0]);
            return team;
        }

        public static List<Team> GetAllTeams()
        {
            List<Team> teams = new List<Team>();
            DataSet ds = GetAllTeamsDataSet();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                teams.Add(MakeTeamFromRow(dr));
            }

            return teams;
        }
        #endregion


        #region Private Methods
        private static Team MakeTeamFromRow(DataRow dr)
        {
            Team team = new Team
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                TeamName = dr[(int)Columns.TeamName].ToString(),
                Website = dr[(int)Columns.Website].ToString(),
                IconURL = dr[(int)Columns.IconLocation].ToString()
            };

            return team;
        }

        private static DataSet GetAllTeamsDataSet()
        {
            return GetAllFromTable("Teams");
        }



        #endregion

        #region Tests

        public static bool Test(out string reason)
        {
            bool result = true;
            reason = null;

            Team team = new Team { TeamName = "Andrew's Test", IconURL = "TEST", Website = "Test" };
            InsertTeam(team);
            if (GetTeam(team.TeamName) == null)
            {
                result = false;
                reason += "Insert or Get Fail. ";
            }

            team.TeamName = "Andrew Test2";
            UpdateTeam(team);

            if (GetTeam(team.TeamName) == null)
            {
                result = false;
                reason += "Update Fail. ";
            }

            string teamName = team.TeamName;

            DeleteTeam(team);

            if (GetTeam(teamName) != null)
            {
                result = false;
                reason += "Delete Fail. ";
            }

            return result;
        }

        #endregion
    }

    public static class Users
    {

        enum Columns { ID, UserName, Password, NickName, Slogan, BannerURL, IconURL }
        public static void InsertUser(User user)
        {
            String sqlStatement = "INSERT INTO Users(UserName, Password, NickName, Slogan, BannerURL, IconURL)" +
                                   "VALUES (@userName, @password, @nickName, @slogan, @bannerURL, @iconURL)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@userName", user.UserName);
            cmd.Parameters.AddWithValue("@password", user.Password);
            cmd.Parameters.AddWithValue("@nickName", user.NickName);
            cmd.Parameters.AddWithValue("@slogan", user.Slogan);
            cmd.Parameters.AddWithValue("@bannerURL", user.BannerURL);
            cmd.Parameters.AddWithValue("@iconURL", user.IconURL);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            user.ID = (int)cmd.LastInsertedId;
        }

        public static void UpdateUser(User user)
        {

            String sqlStatement = "UPDATE Users SET TeamName = @teamName, Password = @password, NickName = @nickName," +
                                   "Slogan = @slogan, BannerURL = @bannerURL, IconURL = @iconURL WHERE ID = @ID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@userName", user.UserName);
            cmd.Parameters.AddWithValue("@password", user.Password);
            cmd.Parameters.AddWithValue("@nickName", user.NickName);
            cmd.Parameters.AddWithValue("@slogan", user.Slogan);
            cmd.Parameters.AddWithValue("@bannerURL", user.BannerURL);
            cmd.Parameters.AddWithValue("@iconURL", user.IconURL);
            cmd.Parameters.AddWithValue("@ID", user.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();
        }

        public static void DeleteUser(User user)
        {
            String sqlStatement = "Delete From Users Where ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@ID", user.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            user = null;
        }

        public static User GetUser(string userName)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Users", "UserName", userName);
            User user = null;
            if (ds.Tables[0].Rows.Count > 0)
                user = MakeUserFromRow(ds.Tables[0].Rows[0]);
            return user;
        }

        public static List<User> GetAllUsers()
        {
            List<User> users = new List<User>();
            DataSet ds = GetAllUsersDataSet();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                users.Add(MakeUserFromRow(dr));
            }

            return users;
        }

        private static User MakeUserFromRow(DataRow dr)
        {
            User user = new User
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                UserName = dr[(int)Columns.UserName].ToString(),
                Password = dr[(int)Columns.Password].ToString(),
                NickName = dr[(int)Columns.NickName].ToString(),
                Slogan = dr[(int)Columns.Slogan].ToString(),
                BannerURL = dr[(int)Columns.BannerURL].ToString(),
                IconURL = dr[(int)Columns.IconURL].ToString()
            };

            return user;
        }

        private static DataSet GetAllUsersDataSet()
        {
            return GetAllFromTable("Users");
        }
    }

    public static class Games
    {
        enum Columns {ID, HomeTeamID, AwayTeamID, Date, HomeScore, AwayScore, State, WeekID}

        public static void InsertGame(Game game)
        {
            String sqlStatement = "INSERT INTO Games (`HomeTeamID`, `AwayTeamID`, `Date`, `HomeScore`, `AwayScore`, `State`, `WeekID`)" +
                                   "VALUES (@homeTeamID, @awayTeamID, @date, @homeScore, @awayScore, @state, @weekID)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@homeTeamID", game.HomeTeam.ID);
            cmd.Parameters.AddWithValue("@awayTeamID", game.AwayTeam.ID);
            cmd.Parameters.AddWithValue("@date", game.Date);
            cmd.Parameters.AddWithValue("@homeScore", game.HomeScore);
            cmd.Parameters.AddWithValue("@awayScore", game.AwayScore);
            cmd.Parameters.AddWithValue("@state", (int)game.GameState);
            cmd.Parameters.AddWithValue("@weekID", game.GameWeek.ID);


            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            game.ID = (int)cmd.LastInsertedId;
        }

        public static void UpdateGame(Game game)
        {
            String sqlStatement = "UPDATE Games SET HomeTeamID = @homeTeamID, AwayTeamID = @awayTeamID, Date = @date," +
                                   "HomeScore = @homeScore, AwayScore = @awayScore, State = @state, WeekID = @weekID WHERE ID = @ID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@homeTeamID", game.HomeTeam.ID);
            cmd.Parameters.AddWithValue("@awayTeamID", game.AwayTeam.ID);
            cmd.Parameters.AddWithValue("@date", game.Date);
            cmd.Parameters.AddWithValue("@homeScore", game.HomeScore);
            cmd.Parameters.AddWithValue("@awayScore", game.AwayScore);
            cmd.Parameters.AddWithValue("@state", (int)game.GameState);
            cmd.Parameters.AddWithValue("@weekID", game.GameWeek.ID);
            cmd.Parameters.AddWithValue("@ID", game.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

        }

        public static void DeleteGame(Game game)
        {
            String sqlStatement = "Delete From Games Where ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@ID", game.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            game = null;
        }

        public static Game GetGame(Team awayTeam, Team homeTeam, Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Games Where AwayTeamID = @awayTeamID AND HomeTeamID = @homeTeamID AND WeekID = @weekID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@awayTeamID", awayTeam.ID);
            cmd.Parameters.AddWithValue("@homeTeamID", homeTeam.ID);
            cmd.Parameters.AddWithValue("@weekID", week.ID);

            mycon.Open();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
            mycon.Close();
            Game game = null;
            if (ds.Tables[0].Rows.Count > 0)
                game = MakeGameFromRow(ds.Tables[0].Rows[0]);

            return game;
        }

        public static List<Game> GetGamesForWeek(Week week)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Games", "WeekID", week.ID.ToString());
            List<Game> games = new List<Game>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                    games.Add(MakeGameFromRow(dr));
            }
            return games;
        }

        private static Game MakeGameFromRow(DataRow dr)
        {
            Game game = new Game
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                AwayTeam = DataBase.Teams.GetTeamWithID(dr[(int)Columns.AwayTeamID].ToString()),
                HomeTeam = DataBase.Teams.GetTeamWithID(dr[(int)Columns.HomeTeamID].ToString()),
                HomeScore = int.Parse((dr[(int)Columns.HomeScore].ToString())),
                AwayScore = int.Parse(dr[(int)Columns.AwayScore].ToString()),
                GameState = (Game.State) int.Parse(dr[(int)Columns.State].ToString()),
                Date = DateTime.Parse(dr[(int)Columns.Date].ToString()),
                GameWeek = DataBase.Weeks.GetWeek(dr[(int)Columns.WeekID].ToString())
            };



            return game;
        }

        public static Game GetGame(string ID)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Games", "ID", ID);
            Game game = null;
            if (ds.Tables[0].Rows.Count > 0)
                game = MakeGameFromRow(ds.Tables[0].Rows[0]);
            return game;
        }

    }

    public static class UserPicks
    {
        enum Columns {UserID, GameID, AwayTeam, HomeTeam, AwayScore, HomeScore}

        public static void InsertUserPick(UserPick userPick)
        {
            String sqlStatement = "INSERT INTO UserPicks (`UserID`, `GameID`, `ChosenTeamID`, `AwayScore`, `HomeScore`)" +
                                "VALUES (@userID, @gameID, @chosenTeamID, @awayScore, @homeScore)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@userID", userPick.PickUser.ID);
            cmd.Parameters.AddWithValue("@gameID", userPick.PickGame.ID);
            cmd.Parameters.AddWithValue("@chosenTeamID", userPick.TeamPicked.ID);
            cmd.Parameters.AddWithValue("@awayScore", userPick.AwayTeamScore);
            cmd.Parameters.AddWithValue("@homeScore", userPick.HomeTeamScore);


            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();
        }

        public static void UpdateUserPick(UserPick userPick)
        {
            String sqlStatement = "UPDATE Games SET ChosenTeamID = @chosenTeamID, AwayScore = @awayScore, HomeScore = @homeScore," +
                       " WHERE UserID = @userID and GameID = @gameID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);   
            cmd.Parameters.AddWithValue("@chosenTeamID", userPick.TeamPicked.ID);
            cmd.Parameters.AddWithValue("@awayScore", userPick.AwayTeamScore);
            cmd.Parameters.AddWithValue("@homeScore", userPick.HomeTeamScore);
            cmd.Parameters.AddWithValue("@userID", userPick.PickUser.ID);
            cmd.Parameters.AddWithValue("@gameID", userPick.PickGame.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();
        }

        public static void DeleteUserPick(UserPick userPick)
        {
            String sqlStatement = "Delete From Games WHERE UserID = @userID and GameID = @gameID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@userID", userPick.PickUser.ID);
            cmd.Parameters.AddWithValue("@gameID", userPick.PickGame.ID);

            mycon.Open();
            cmd.ExecuteNonQuery();
            mycon.Close();

            userPick = null;
        }

        public static UserPick GetUserPick(Game game, User user)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From UserPicks WHERE UserID = @userID and GameID = @gameID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@userID", user.ID);
            cmd.Parameters.AddWithValue("@gameID", game.ID);

            mycon.Open();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
            mycon.Close();
            UserPick userPick = null;
            if (ds.Tables[0].Rows.Count > 0)
                userPick = MakeUserPickFromRow(ds.Tables[0].Rows[0]);

            return userPick;
        }

        public static List<UserPick> GetAllUserPicksForGame(Game game)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("UserPicks", "GameID", game.ID.ToString());
            List<UserPick> userPicks = new List<UserPick>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    userPicks.Add(MakeUserPickFromRow(dr));
            }
            return userPicks;
        }

        public static List<UserPick> GetAllUserPicksForWeekAndUser(User user, Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From UserPicks INNER JOIN Games ON UserPicks.GameID = Games.ID WHERE UserPicks.UserID = @userID and Games.WeekID = @weekID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@userID", user.ID);
            cmd.Parameters.AddWithValue("@week", week.ID);

            mycon.Open();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
            mycon.Close();
            List<UserPick> userPicks = new List<UserPick>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    userPicks.Add(MakeUserPickFromRow(dr));
            }
            return userPicks;
        }

        public static List<UserPick> GetAllUserPicksForWeek(Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From UserPicks INNER JOIN Games ON UserPicks.GameID = Games.ID WHERE Games.WeekID = @weekID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
            cmd.Parameters.AddWithValue("@week", week.ID);

            mycon.Open();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
            mycon.Close();
            List<UserPick> userPicks = new List<UserPick>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    userPicks.Add(MakeUserPickFromRow(dr));
            }
            return userPicks;
        }

        public static UserPick MakeUserPickFromRow(DataRow dr)
        {
            UserPick game = new UserPick
            {
                PickGame = Games.GetGame(dr[(int)Columns.GameID].ToString()),
                TeamPicked = Teams.GetTeamWithID(

            };



            return game;
        }

        

        
    }

    public static class Weeks
    {
        enum Columns {ID, Number, StartDate, EndDate, Season}
        public static Week GetWeek(string number, Season season)
        {
        }

        public static Week GetWeek(string weekID)
        {
        }

    }

    public static class Season
    {
        enum Columns { ID, StartYear, EndYear, CurrentSeason };

        public static Season GetCurrentSeason()
        {
        }

    }
}